IF OBJECT_ID('ssf.job_run_job_as_jobstep') IS NOT NULL
	DROP PROCEDURE ssf.job_run_job_as_jobstep
GO

CREATE PROCEDURE ssf.job_run_job_as_jobstep
	@job_name NVARCHAR(128)
	, @poll_interval TIME(1) = NULL
	, @run_status INT = NULL OUTPUT
	, @token_STEPID INT = NULL
	, @token_DATE VARCHAR(8) = NULL
	, @token_TIME VARCHAR(8) = NULL
	, @token_JOBID UNIQUEIDENTIFIER = NULL
	, @token_STRTDT VARCHAR(8) = NULL
	, @token_STRTTM VARCHAR(8) = NULL
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare logging variables]]></code_comment>*/
DECLARE	@log_id BIGINT
			, @task NVARCHAR(512)
			, @task_description NVARCHAR(2048)
			, @target_object NVARCHAR(512)
			, @source_object NVARCHAR(512) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)
-- ============

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@jobstep_start_datetime DATETIME = NULL
			, @parent_job_name VARCHAR(128) = NULL
			, @parent_job_start_datetime DATETIME = NULL
			, @jobstep_run_id XML
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameters]]></code_comment>*/
SET @run_status = NULL
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@job_name, N'') IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[set variables]]></code_comment>*/
IF @token_JOBID IS NOT NULL
	SET @parent_job_name = ssf.job_get_job_name(@token_JOBID)

SET @jobstep_start_datetime = CASE WHEN @token_DATE IS NULL
	THEN NULL
	WHEN ISDATE(@token_DATE) = 0
	THEN NULL
	ELSE ssf.job_run_datetime(@token_DATE, ISNULL(@token_TIME, '0'))
	END

SET @parent_job_start_datetime = CASE WHEN @token_STRTDT IS NULL
	THEN NULL
	WHEN ISDATE(@token_STRTDT) = 0
	THEN NULL
	ELSE ssf.job_run_datetime(@token_STRTDT, ISNULL(@token_STRTTM, '0'))
	END

SET @jobstep_run_id = ssf.job_generate_jobstep_run_id(@job_name, @token_STEPID, @jobstep_start_datetime, @parent_job_name, @parent_job_start_datetime)
-- ============

-- ============
/*<code_comment><![CDATA[run job]]></code_comment>*/
SET @task = 'RUN JOB'
SET @task_description = CAST(@jobstep_run_id AS NVARCHAR(512))
	+ ': Run job <' + @job_name + '>.'
	+ '  Use ssf.job_run_until_complete to run the job synchronously.'
SET @target_object = @job_name

EXECUTE ssf.log_begin
	@source_object = @source_object
	, @task = @task
	, @task_description = @task_description
	, @target_object = @target_object
	, @log_id = @log_id OUTPUT

BEGIN TRY
	EXECUTE @proc_error = ssf.job_run_until_complete
		@job_name = @job_name
		, @poll_interval = @poll_interval
		, @run_status = @run_status OUTPUT

	SELECT @sql_error = @@ERROR
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
		, @error_line = ERROR_LINE()
END CATCH

SET @sql_error = ISNULL(NULLIF(@sql_error, 0), @proc_error)

EXECUTE ssf.log_end
	@log_id = @log_id
	, @log_error_number = @sql_error

IF @sql_error <> 0
BEGIN
	RAISERROR('The call to the procedure <ssf.job_run_until_complete> failed.', 16, 1)
	RETURN @sql_error
END
-- ============


RETURN @@ERROR
GO
